﻿using System;
using System.Data;
using MySql.Data.MySqlClient;

namespace MuZa.DBConnector
{
    internal static class Connector
    {
        private const string Host = "localhost";
        private const string Database = "muza_scenario_3";
        private const string User = "root";
        private const string Password = "root";
        private static string _strProvider = "Data Source=" + Host + ";Database=" + Database + ";User ID=" + User + ";Password=" + Password;
        private static MySqlConnection _msqlConnection = new MySqlConnection(_strProvider);
        private static MySqlDataAdapter _dbAdapter = new MySqlDataAdapter();

        private static void Init()
        {
            _strProvider = "Data Source=" + Host + ";Database=" + Database + ";User ID=" + User + ";Password=" + Password;
            //define the connection reference and initialize it
            _msqlConnection = new MySqlConnection(_strProvider);
            _dbAdapter = new MySqlDataAdapter();
        }

        public static bool ConnectDB(string sqlString, ref DataTable dTable)
        {
            //query:
            MySqlConnection _msqlConnection = new MySqlConnection(_strProvider);
            MySqlDataAdapter _dbAdapter = new MySqlDataAdapter();

            dTable = new DataTable();
            try
            {
                //open the connection
                //Init();
                _msqlConnection.Open();

                MySqlCommand mysqlCommand = new MySqlCommand(sqlString, _msqlConnection);
                _dbAdapter = new MySqlDataAdapter(mysqlCommand);

                //fill the DataTable
                _dbAdapter.Fill(dTable);
            }
            catch (Exception ex)
            {
                //do something with the exception
                Console.WriteLine(ex.Message);
                Console.WriteLine(sqlString);

                //Thread.Sleep(100);
                //goto query;

                return false;
            }
            finally
            {
                //always close the connection
                _msqlConnection.Close();
                //while (true)
                //{
                //    if (_msqlConnection.State == ConnectionState.Open || _msqlConnection.State != ConnectionState.Executing)
                //    {
                //        _msqlConnection.Close();
                //        Thread.Sleep(100);
                //    }
                //}
            }

            return true;
        }

        public static bool ConnectDB(string sqlString)
        {
            //Init();
            //query:
            MySqlConnection _msqlConnection = new MySqlConnection(_strProvider);
            MySqlDataAdapter _dbAdapter = new MySqlDataAdapter();

            try
            {
                //open the connection
                //Init();
                _msqlConnection.Open();

                MySqlCommand mysqlCommand = new MySqlCommand(sqlString, _msqlConnection);
                mysqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                //do something with the exception
                Console.WriteLine(ex.Message);
                Console.WriteLine(sqlString);

                //Thread.Sleep(100);
                //goto query;

                return false;
            }
            finally
            {
                //always close the connection
                //if (_msqlConnection.State == ConnectionState.Open)
                _msqlConnection.Close();
            }

            return true;
        }

        //public static bool ConnectDB(string sqlString, DataTable dTable)
        //{
        //    Init();
        //    dTable = new DataTable();
        //    try
        //    {
        //        //open the connection
        //        _msqlConnection.Open();

        //        MySqlCommand mysqlCommand = new MySqlCommand(sqlString, _msqlConnection);
        //        _dbAdapter = new MySqlDataAdapter(mysqlCommand);

        //        //fill the DataTable
        //        _dbAdapter.Fill(dTable);
        //    }
        //    catch (Exception ex)
        //    {
        //        //do something with the exception
        //        Console.WriteLine(ex.Message);
        //        Console.WriteLine(sqlString);

        //        return false;
        //    }
        //    finally
        //    {
        //        //always close the connection
        //        _msqlConnection.Close();
        //    }

        //    return true;
        //}

        //public static int ConnectDBforId(string sqlString)
        //{
        //    Init();
        //    var dTable = new DataTable();

        //    try
        //    {
        //        //open the connection
        //        _msqlConnection.Open();

        //        _dbAdapter = new MySqlDataAdapter(sqlString, _msqlConnection);
        //        new MySqlCommandBuilder(_dbAdapter);

        //        //create a DataTable to hold the query results

        //        //fill the DataTable
        //        _dbAdapter.Fill(dTable);
        //    }
        //    catch (Exception ex)
        //    {
        //        //do something with the exception
        //        Console.WriteLine(ex.Message);
        //        Console.WriteLine(sqlString);
        //    }
        //    finally
        //    {
        //        //always close the connection
        //        _msqlConnection.Close();
        //    }
        //    var id = (int)dTable.Rows[0].ItemArray[0];
        //    return id;
        //}
    }
}